﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;using System.Configuration;
using System.Windows.Forms;

namespace ChairtyBank.DAL.PreAccept
{
    public class ClientEntity
    {
      public Int32 C_Id { get; set;}
      public string C_Name { get; set;}
      public string C_Gender { get; set;}
      public string C_Phone { get; set;}
      public string C_Mobile { get; set;}
      public string C_Region { get; set;}
      public string C_Address { get; set; }
    }

    public class Client
    {
        //Set absolute DB path rather than relative to solve openFileDialog reset path problem
        private static string ConnString = ConfigurationManager.ConnectionStrings["BankElKheir"].ToString();
            //ConfigurationManager.ConnectionStrings["BankElKheir"].ToString();
        private static string CmdText = "";

        public static bool Add(ClientEntity s)
        {
            bool flag = true;

            CmdText = "Insert into Client (C_Id,C_Name,C_Gender,C_Phone,C_Mobile,C_Region,C_Address) values"+
                " (@C_Id,@C_Name,@C_Gender,@C_Phone,@C_Mobile,@C_Region,@C_Address)";

            SqlConnection Con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@C_Id", s.C_Id);
            cmd.Parameters.AddWithValue("@C_Name", s.C_Name);
            cmd.Parameters.AddWithValue("@C_Gender", s.C_Gender);
            cmd.Parameters.AddWithValue("@C_Phone", s.C_Phone);
            cmd.Parameters.AddWithValue("@C_Mobile", s.C_Mobile);
            cmd.Parameters.AddWithValue("@C_Region", s.C_Region);
            cmd.Parameters.AddWithValue("@C_Address", s.C_Address);

            try
            {
                Con.Open();
                int Num = cmd.ExecuteNonQuery();
                if (Num == 0)
                    flag = false;
            }

            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى ادخال البيانات" + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                    flag = false;
                }
            }

            return flag;


        }

        public static bool Update(ClientEntity s)
        {

            bool flag = true;

            CmdText = "Update Client set C_Name = @C_Name , C_Gender = @C_Gender , C_Phone = @C_Phone , C_Mobile = @C_Mobile ,  C_Region  = @C_Region , C_Address = @C_Address WHERE C_Id= @C_Id ";

            SqlConnection Con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(CmdText, Con);


            cmd.Parameters.AddWithValue("@C_Name", s.C_Name);
            cmd.Parameters.AddWithValue("@C_Gender", s.C_Gender);
            cmd.Parameters.AddWithValue("@C_Phone", s.C_Phone);
            cmd.Parameters.AddWithValue("@C_Mobile", s.C_Mobile);
            cmd.Parameters.AddWithValue("@C_Region", s.C_Region);
            cmd.Parameters.AddWithValue("@C_Address", s.C_Address);
            cmd.Parameters.AddWithValue("@C_Id", s.C_Id);

            try
            {
                Con.Open();
                int num = cmd.ExecuteNonQuery();
                if (num == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى تغيير البيانات " + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                    flag = false;
                }
            }

            return flag;

        }

        public static bool Delete(Int32 s)
        {
            bool flag = true;

            CmdText = "Delete from Client where C_Id = @C_Id";

            SqlConnection Con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@C_Id",s);

            try
            {
                Con.Open();
                int num = cmd.ExecuteNonQuery();
                if (num == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ اثناء الحذف ", "رساله ادارية " + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                    flag = false;
                }
            }

            return flag;
        }

        public static List<ClientEntity> GetClientByID(Int32 id)
        {
            ClientEntity x;
            List<ClientEntity> ClientList = new List<ClientEntity>();

            CmdText = "Select * from Client where C_Id = @C_Id";

            SqlConnection Con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@C_Id", id);

            try
            {
                Con.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                    ClientList.Add(x);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return ClientList;
        }

        public static List<ClientEntity> GetClientByName(string Name)
        {
            {
                ClientEntity x;
                List<ClientEntity> ClientList = new List<ClientEntity>();

                CmdText = "Select * from Client where C_Name Like @C_Name ";

                SqlConnection Con = new SqlConnection(ConnString);
                SqlCommand cmd = new SqlCommand(CmdText, Con);

                cmd.Parameters.AddWithValue("@C_Name", Name);

                try
                {
                    Con.Open();

                    SqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        x = FillEntityFromReader(reader);
                        ClientList.Add(x);
                    }

                    reader.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
                }
                finally
                {
                    if (Con.State == ConnectionState.Open)
                        Con.Close();
                }

                return ClientList;
            }
        }

        private static ClientEntity FillEntityFromReader(SqlDataReader reader)
        {
            ClientEntity x = new ClientEntity();
            x.C_Id = Int32.Parse(reader["C_Id"].ToString());
            if (reader["C_Name"] != null)
                x.C_Name = reader["C_Name"].ToString();
            if (reader["C_Gender"] != null)
                x.C_Gender = reader["C_Gender"].ToString();
            if ((reader["C_Phone"] != null))
                x.C_Phone = reader["C_Phone"].ToString();
            if (reader["C_Mobile"] != null)
                x.C_Mobile = reader["C_Mobile"].ToString();
            if (reader["C_Address"] != null)
                x.C_Address = reader["C_Address"].ToString();
            if (reader["C_Region"] != null)
                x.C_Region = reader["C_Region"].ToString();
       
            return x;
        }
    }
}
